﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace MoneyClass
{
    public class Classify
    {
        public static DataTable getAll()
        {
            string sql = "select [guid],[classify],iif(type=0,'收入','支出') as typeText,type from [QT_User_Classify] order by [type],[classify] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static DataTable get(string uid)
        {
            string sql = "select [guid],[classify],iif(type=0,'收入','支出') as typeText,type from [QT_User_Classify] where [uid]='" + uid + "' order by [type],[classify] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static DataTable getIncome(string uid)
        {
            string sql = "select [guid],[classify] from [QT_User_Classify] where [uid]='" + uid + "' and [type]=0 order by [classify] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static DataTable getExpense(string uid)
        {
            string sql = "select [guid],[classify] from [QT_User_Classify] where [uid]='" + uid + "' and [type]=1 order by [classify] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            return dt;
        }

        public static int Add(string classify, int type, string uid)
        {
            if (Exsits(null, classify, type, uid)) return -2;

            string sql = "insert into [QT_User_Classify]([guid],[classify],[type],[uid]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + classify + "'," + type + ",'" + uid + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Update(string guid, string classify, int type, string uid)
        {
            if (Exsits(guid, classify, type, uid)) return -2;

            string sql = "update [QT_User_Classify] set [classify]='" + classify + "',[type]=" + type + " where [guid]='" + guid + "' and [uid]='" + uid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Delete(string guid)
        {
            string sql = "delete from [QT_User_Classify] where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static int Delete(List<string> guids)
        {
            string sql = "delete from [QT_User_Classify] where [guid] in('" + string.Join("','", guids.ToArray()) + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connectionString).ExecuteNonQuery(sql);
            return rval;
        }

        public static bool Exsits(string guid, string classify,int type, string uid)
        {
            string sql = "select [guid] from [QT_User_Classify] where [uid]='" + uid + "' and [classify]='" + classify + "' and [type]=" + type + (string.IsNullOrEmpty(guid) ? "" : (" and [guid]<>'" + guid + "'"));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connectionString).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            return false;
        }
    }
}
